﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace HotelManagement.Class
{
    public class HotelDBManager
    {
        public HotelDBManager() { }

        public static UserAccount getUserInfo(string username,string password) {
            UserAccount us = null;
            try
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                con.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = con;
                command.CommandText = "SELECT * FROM UserAccount WHERE UserID=@username AND Password=@password";
                command.Parameters.AddWithValue("@username", username);
                command.Parameters.AddWithValue("@password", password);
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    us = new UserAccount();
                    us.UserID = (string)dr["UserID"];
                    us.StaffID = (string)dr["StaffID"];
                    us.FirstName = (string)dr["FirstName"];
                    us.LastName = (string)dr["LastName"];
                    us.Email = (string)dr["Email"];
                    us.Password=(string)dr["Password"];
                    us.Role = (string)dr["Role"];

                }
                dr.Close();
                con.Close();
            }
            catch (SqlException exp)
            {
                throw exp;
            }
            return us;
        }

        public static Staff getStaffInfoByStaffID(string sID)
        {
            Staff s = null;
            try
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                con.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = con;
                command.CommandText = "SELECT * FROM Staff WHERE StaffID=@sid";
                command.Parameters.AddWithValue("@sid",sID);
                
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    s = new Staff();
                    s.StaffID = (string)dr["StaffID"];
                    s.FirstName = (string)dr["FirstName"];
                    s.LastName = (string)dr["LastName"];
                    DateTime dt=(DateTime)dr["DOB"]; //date to string
                    s.DateOfBirth=Convert.ToString(dt);
                    s.Gender=(string)dr["Gender"];
                    s.PostalCode = (string)dr["PostalCode"];
                    s.Country = (string)dr["Country"];
                    s.DutyType = (string)dr["DutyType"];
                    s.Title = (string)dr["Title"];
                    s.MobileNum = (int)dr["MobileNum"];
                    s.HomeNum = (int)dr["HomeNum"];
                    s.BlockNum = (string)dr["BlockNum"];
                    s.StreetName = (string)dr["StreetName"];
                    s.UnitNum = (string)dr["UnitNum"];
                    s.EmailAddress= (string)dr["Email"];
                    
                }
                dr.Close();
                con.Close();
            }
            catch (SqlException exp)
            {
                throw exp;
            }
            return s;
        }

        public static int updateStaffInfoByID(int id, Staff stf)
        {
            int i = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand com = new SqlCommand();
                com.Connection = conn;
                com.CommandText = "UPDATE Staff SET FirstName=@firstname, LastName=@lastname, MobileNum=@mobile, HomeNum=@home, Email=@email ,"+
                "BlockNum=@block, StreetName=@street, UnitNum=@unit, PostalCode=@postal WHERE StaffID=@id";
                com.Parameters.AddWithValue("@firstname", stf.FirstName);
                com.Parameters.AddWithValue("@lastname",stf.LastName);
                com.Parameters.AddWithValue("@mobile", stf.MobileNum);
                com.Parameters.AddWithValue("@home", stf.HomeNum);
                com.Parameters.AddWithValue("@email", stf.EmailAddress);
                com.Parameters.AddWithValue("@block",stf.BlockNum);
                com.Parameters.AddWithValue("@street", stf.StreetName);
                
                com.Parameters.AddWithValue("@unit",stf.UnitNum);
                com.Parameters.AddWithValue("@postal", stf.PostalCode);
                com.Parameters.AddWithValue("@id", id);
                i = com.ExecuteNonQuery(); //execute query 1

                com.CommandText = "UPDATE UserAccount SET FirstName=@fname, LastName=@lname, Email=@mail WHERE StaffID=@pk";
                com.Parameters.AddWithValue("@fname", stf.FirstName);
                com.Parameters.AddWithValue("@lname", stf.LastName);
                com.Parameters.AddWithValue("@mail", stf.EmailAddress);
                com.Parameters.AddWithValue("@pk", id);
                i = com.ExecuteNonQuery(); //execute query 2
                conn.Close();
            }
            catch (SqlException exp)
            {
                throw exp;
            }
            return i;
        }

        public static int updatePasswordByUserID(string username, string newpassword) //update password only
        {
            int i = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand com = new SqlCommand();
                com.Connection = conn;
                com.CommandText = "UPDATE UserAccount SET Password=@pass WHERE UserID=@id";
                com.Parameters.AddWithValue("@pass", newpassword);
                com.Parameters.AddWithValue("@id",username);
                i = com.ExecuteNonQuery(); //execute query
                conn.Close();
            }
            catch (SqlException exp)
            {
                throw exp;
            }
            return i;
        }

        public static int deleteAccountByID(string id) //delete account
        {
            int i = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand com = new SqlCommand();
                com.Connection = conn;
                com.CommandText = "DELETE FROM UserAccount WHERE UserID=@id";
                com.Parameters.AddWithValue("@id", id);
                i = com.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException exp)
            {
                throw exp;
            }
            return i;
        }

        public static ArrayList getStaffDetailsByDutyType(string dutyType) //filter by duty Type
        {
            try
            {
                ArrayList list;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "SELECT StaffID, FirstName, LastName, Gender, DutyType, Title FROM Staff WHERE DutyType=@type";
                command.Parameters.AddWithValue("@type", dutyType);
                SqlDataReader dr = command.ExecuteReader();
                list = new ArrayList();
                while (dr.Read())
                {
                    Staff s = new Staff();
                    s.StaffID = (string)dr["StaffID"];
                    s.FirstName = (string)dr["FirstName"];
                    s.LastName=(string)dr["LastName"];
                    s.Gender=(string)dr["Gender"];
                    s.DutyType = (string)dr["DutyType"];
                    s.Title = (string)dr["Title"];
                    list.Add(s);
                }
                dr.Close();
                conn.Close();
                return list;
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

        public static UserAccount accountAlreadyExists(string sID)
        {
            UserAccount us = null;
            try
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                con.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = con;
                command.CommandText = "SELECT * FROM UserAccount WHERE StaffID=@sid";
                command.Parameters.AddWithValue("@sid", sID);

                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    us = new UserAccount();
                    us.UserID = (string)dr["UserID"];
                    us.StaffID = (string)dr["StaffID"];
                    us.FirstName = (string)dr["FirstName"];
                    us.LastName = (string)dr["LastName"];
                    us.Email = (string)dr["Email"];
                    us.Password = (string)dr["Password"];
                    us.Role = (string)dr["Role"];
                }
                dr.Close();
                con.Close();
            }
            catch (SqlException exp)
            {
                throw exp;
            }
            return us;
        }
        public static int createStaffAccount(Staff stf,string username,string password)
        {
            int i = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand com = new SqlCommand();
                com.Connection = conn;
                com.CommandText = "INSERT INTO UserAccount VALUES (@userid,@stfid,@fname,@lname,@email,@password,@role)";
                com.Parameters.AddWithValue("@userid",username );
                com.Parameters.AddWithValue("@stfid", stf.StaffID);
                com.Parameters.AddWithValue("@fname", stf.FirstName);
                com.Parameters.AddWithValue("@lname", stf.LastName);
                com.Parameters.AddWithValue("@email", stf.EmailAddress);
                com.Parameters.AddWithValue("@password",password);
                com.Parameters.AddWithValue("@role", stf.DutyType);
                i = com.ExecuteNonQuery();
                conn.Close();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                if (ex.Number == 2627) {
                    i = 2;
                }
                else
                    throw;
            }
            return i;
        }
    }
}